package util;


import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;


public class JdbcUtil {
    // 使用druid连接池完成连接的获取
    // 将属性书写为静态属性 通过静态代码块进行赋值
    private static DataSource ds;

    // 在静态代码块中读取配置文件创建线程池对象
    static {
        try {
            Properties p = new Properties();
            InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            p.load(is);
            ds = DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    // 获取连接方法
    public static Connection getCon() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 关闭方法
    // 对于连接池中的连接对象 调用close时不是关闭而是归还
    // 需要书写多个关闭释放资源方法
    private static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection con) {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 关闭更新语句连接资源
    public static void close(Statement statement, Connection con) {
        close(statement);
        close(con);
    }

    // 关闭查询语句连接资源
    public static void close(ResultSet rs, Statement statement, Connection con) {
        close(rs);
        close(statement);
        close(con);
    }

    // dml更新方法
    // 使用不确定参数为占位符进行赋值 (未解决事务)
    public static int DML(String sql, Object... values) {
        Connection con = getCon();
        PreparedStatement statement = null;
        int count = 0;
        try {
            statement = con.prepareStatement(sql);
            for (int i = 0; i < values.length; i++) {
                statement.setObject(i + 1, values[i]);
            }
            count = statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(statement, con);
        }
        return count;
    }

    // dml更新方法
    // 使用不确定参数为占位符进行赋值 (解决事务)
    public static int DML(Connection con, String sql, Object... values) throws SQLException {
        // 开启事务
        PreparedStatement statement = null;
        int count = 0;
        con.setAutoCommit(false);
        statement = con.prepareStatement(sql);
        for (int i = 0; i < values.length; i++) {
            statement.setObject(i + 1, values[i]);
        }
        count = statement.executeUpdate();

        return count;
    }

    // dql语句执行功能
    public static <E> ArrayList<E> DQL(Class<E> c, String sql, Object... values) {
        ArrayList<E> list=new ArrayList<>();
        Connection con = getCon();
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            statement = con.prepareStatement(sql);
            //获取原数据对象 将列数以及对应的列名获取
            ResultSetMetaData metaData = statement.getMetaData();
            int columnCount = metaData.getColumnCount();
            //根据列数创建对应长度的数组 并进行列名的存储
            String [] names=new String[columnCount];
            for (int i = 0; i < names.length; i++) {
                names[i]=metaData.getColumnName(i+1);
            }
            //System.out.println(Arrays.toString(names));
            for (int i = 0; i < values.length; i++) {
                statement.setObject(i + 1, values[i]);
            }
            rs = statement.executeQuery();
            while(rs.next()){
                //通过反射创建指定对象
                E e=c.newInstance();

                //遍历列名数组 通过反射获取对应字段对象
                for (String filedName : names) {
                    //获取对应字段对象
                    Field field = c.getDeclaredField(filedName);
                    //获取对应列当前行的值
                    Object value = rs.getObject(filedName);
                    field.setAccessible(true);
                    field.set(e, value);
                }
                list.add(e);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close(rs, statement, con);
        }
        return list;

    }
}

